IF OBJECT_ID('dbo.RptInvestigatorActivity') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptInvestigatorActivity
    IF OBJECT_ID('dbo.RptInvestigatorActivity') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptInvestigatorActivity >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptInvestigatorActivity >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptInvestigatorActivity
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 5/24/2007
--
-- Description: Source of Report, "Investigator Activity Report"
--              In Insight DB Reporting Services
--
-- Parameters:  
--     @PrincInvIds            Semi-colon separated list of PrincipalInvestigator Ids
--     @ResponsibleInstitution Such as "BWH", or "MGH"
--
--              
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.RptInvestigatorActivity()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 5/24/07     KC        Created
-- 5/30/07     KC        Added Fields
-- 6/05/07     KC        Added legacy Parameters
-- 6/11/07     KC        Added OtherRoleSearch and Program Summarization
-- 6/16/07     KC        Removed unused search params.
-- 7/12/07     KC        Instituted naming convention changes. Finalized.
-- 7/20/07     KC        Removed join to View, dbo.vwAdmPrincipalInvestigator
-- 7/23/07     KC        Fact table now has PropNumber on it.
-- 8/4/07      KC        QA Feedback Changes, fixed BudgetDates
--                       changed for a single PI.
-- 8/7/07      KC        Added '0' for 'ALL' in Params
--                       Fixed PercentEffort and Program Data
-- 8/8/07      KC        Efforts sometimes returned multiple rows due to 
--                       data issue with multiple PI's. Added check for AdmPersonId
-- 8/8/07      KC        Made Security Inner Join
-- 8/13/07     KC        Finished changing search for other roles.
-- 8/14/07     KC        Fixed some bugs after QA Feedback.
-- 8/17/07     KC        Security changed to Cached table
-- 8/20/07     KC        Fixed problem with multiple records (due to roles)
--                            and added a few fields for troubleshooting.
-- 8/22/07     KC        Fixed BudgetAmounts (new source)
-- 8/23/07     KC        Moved all Budget/Program joins to self-contained Views
-- 8/27/07     KC        Program Totals were off, and ProgramDirector missing
--                       More bug fixes for Status Search
--                       Removed JOIN on SvcEntityId
-- 9/8/07      KC        Changed to new source tables
-- 9/20/07     KC        Fixed SponsorAgreementID
-- 9/25/07     KC        Used ID's for Inst,Dept,Unit
-- 10/1/07     KC        Fixed outdated function reference. fnGetMultiCriteria
-- 10/10/07    KC        Fix for ProjectFundNumber
-- 11/5/07     KC        Changed PrincipleInvestigator to PrincipalInvestigator
-- 11/14/07    KC        Added check for NULL on AI.ProjectEndDate
-- 11/15/07    KC        Removed check for NULL on AI.ProjectEndDate based requirements
-- 12/17/08    KC        New Security function, and fixed OUTER JOIN 
-- 01/05/08    KC        Security Function Perf Issues: use TempTable
-- 05/05/09	aab		assigned correct columns for pending and awarded efforts (reversed assignments)
						--		assigned correct column to current proposal status,
						--		renamed 'overall status' label to 'current proposal status'
						--		populated institution and department header parameters with data
						--		changed sort order definition to sort by current proposal status values
-- =======================================================================
CREATE PROCEDURE dbo.RptInvestigatorActivity
     (
     @Institution                     VARCHAR(100) = NULL,
     @Department                      VARCHAR(100) = NULL,
     @PrincipalInvestigator           VARCHAR(100),       
     @DomainUserId                    INT
     )
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error INT,
               @RowCount INT,
               @PISearchManagerName    VARCHAR(50),
               @PIInstitution          VARCHAR(50),
               @PIDepartment           VARCHAR(50)

     -- ALL checking
     IF @Institution IN ('ALL','','0') SET @Institution = NULL
     IF @Department IN ('ALL','','0') SET @Department = NULL
     
     -- Get The Search Name
     SELECT @PISearchManagerName = dbo.fnGetPersonName( CAST(@PrincipalInvestigator AS INT)) + ' ' +
               ISNULL(dbo.fnGetDegree(@PrincipalInvestigator),'')

     -- Need to Get Institution Name and Department for PI               
     SELECT    @PIInstitution = O.Institution,
               @PIDepartment = O.Name
     FROM      dbo.AdmPerson AS P LEFT OUTER JOIN
               dbo.AdmInternalPerson  IP ON P.Id = IP.AdmPersonId LEFT OUTER JOIN
               dbo.AdmPerson_FwkDomainOrganization_Map  DOM on IP.AdmPersonId = DOM.AdmPersonId LEFT OUTER JOIN --
               dbo.FwkDomainOrganization  O ON DOM.FwkDomainOrganizationId = O.Id 
     WHERE     P.Id = @PrincipalInvestigator               

     -- Performance Issues, Get Secured AgreementIds into a temp table
     CREATE TABLE   #tt_Agreements  (AgreementId INT)
     
     INSERT    #tt_Agreements (AgreementId)
     SELECT    AgreementId
     FROM      dbo.fnGetSecurityAgreements(@DomainUserId) 

     ---------------------------------------------------------------------
     -- RESULT Query:
     ---------------------------------------------------------------------
     SELECT    AI.FolderNumber AS ProposalNumber,
               AI.DocumentNumber as DocumentNumber,
               AI.PrincipalInvestigator AS PrincipalInvestigator,
               CASE WHEN (ARE.StaffedRoleName IS NULL OR RTRIM(ARE.StaffedRoleName) = '') AND AI.PrincipalInvestigatorId = @PrincipalInvestigator
                    THEN 'Principal Investigator'
                    ELSE ARE.StaffedRoleName
               END AS InvestigatorRole,
               AI.ProposalTitle AS Title,
               AI.Institution AS PIInstitution,
               AI.Department AS PIDepartment,
               AI.ChiefCode AS ChiefCode,
               AI.Unit AS UnitCode,
               AI.SponsorName AS SponsorName,
               AI.SponsorAgreementID AS SponsorAgreementID,
               AI.SponsorTypeName AS SponsorType,
               CONVERT(VARCHAR(10),PROG.ProgramStartDate,101) AS ProgramStartDate,
               CONVERT(VARCHAR(10),PROG.ProgramEndDate,101) AS ProgramEndDate,
               CONVERT(VARCHAR(10),AI.ProjectStartDate,101) AS ProjectStartDate,
               CONVERT(VARCHAR(10),AI.ProjectEndDate,101) AS ProjectEndDate,
               CONVERT(VARCHAR(10),BUDG.BudgetStartDate,101) AS BudgetStartDate,
               CONVERT(VARCHAR(10),BUDG.BudgetEndDate,101) AS BudgetEndDate,
               BUDG.BudgetViewType AS BudgetPeriodStatus,
               ISNULL(PROG.ProgramDirectCost,'') AS DirectCostforProgramPeriod,
               ISNULL(PROJ.ProjectDirectCost,'') AS DirectCostforProjectPeriod,
               ISNULL(BUDG.BudgetDirectCost,'') AS DirectCostforBudgetPeriod,
               ISNULL(PROJ.ProjectIndirectCost,'') AS FAforProjectPeriod,
               ISNULL(BUDG.BudgetIndirectCost,'') AS FAforBudgetPeriod,
               AI.ProposalShortTitle AS ProposalShortTitle,
               PROG.ProgramShortTitle AS ProgramShortTitle,
               PROG.ProgramDirector AS ProgramDirector,
               -- If ProjectEndDate has passed, but Status is not closed, tag as Ended
               /*
				AI.MasterProposalStatus AS ProposalStatus,--changed by aab on 5/6/09
               CASE WHEN AI.MasterProposalStatus = 'Awarded' THEN 1
                    WHEN AI.MasterProposalStatus = 'Pending' THEN 2
                    WHEN AI.MasterProposalStatus = 'Ended' THEN 3
                    WHEN AI.MasterProposalStatus = 'Closed' THEN 4
                    ELSE 5 
			  END AS SortOrder,*/
			   AI.ProposalStatus AS ProposalStatus,--added by aab on 5/6/09
			  CASE WHEN AI.ProposalStatus = 'Fully Executed/Awarded - Activated' THEN 1
                    WHEN AI.ProposalStatus = 'Approved' THEN 2
					WHEN AI.ProposalStatus like 'Pending%' THEN 3
                    WHEN AI.ProposalStatus = 'Closed' THEN 4
                    ELSE 5 
			  END AS SortOrder,
               AI.OverallStatus AS OverallStatus,
               AI.TransactionType AS TransactionType,
               AI.AgreementType AS ProposalType,
               AI.ActivityDescription AS ActivityType,
               ISNULL(ARE.PendingEffort,0.0) AS PendingEffort,
               ISNULL(ARE.CommittedEffort,0.0) AS CommittedEffort,
               BUDG.IndirectCostRate AS ICRate,
               ARE.AdmPersonId AS EffortAdmPersonId,
               @PISearchManagerName AS PISearchManagerName,
               BUDG.IndirectCostBasis AS ICBase,
               ISNULL(BUDG.FundNumber,AI.ProjectFundNumber) AS PSFundNumber,
               BUDG.BudgetYear AS BudgetYear,
               AI.PrincipalInvestigatorId AS PrincipalInvestigatorId,
               AI.ResourceId AS AgrResourceId,
               AI.SvcEntityId AS SvcEntityId,
               AI.AgreementId AS AgreemendId,
               @Institution AS Institution,
               @Department AS Department
     FROM      dbo.AgrAgreement_Info AS AI LEFT OUTER JOIN

               -- Program Level Data, via dbo.vwAgreementPrograms (using dbo.AgrAgreementHierarchy)
               -- View Returns Program data using Child FolderNumber
               dbo.vwAgreementPrograms PROG ON PROG.FolderNumber = AI.FolderNumber  LEFT OUTER JOIN
               
               -- ProjectDirectCosts have to be summed from the Budget tables
               -- Over the life of the Agreement (Project)
               dbo.vwAgreementProjectBudgets PROJ ON AI.AgreementId = PROJ.AgreementId LEFT OUTER JOIN
               
               -- We need only the latest BudgetYear and BudgetDates for each 
               -- FolderNumber within the search date params.
               dbo.vwAgreementCurrentBudgets BUDG ON AI.AgreementId = BUDG.AgreementId INNER JOIN
               
               -- Security Issues: Not all Users can see all Agreements!
               #tt_Agreements AUTH ON AI.AgreementId = AUTH.AgreementId LEFT OUTER JOIN 

               -- Effort 
               dbo.vwAgreementRolesandEfforts ARE ON AI.SvcEntityId = ARE.SvcEntityId AND BUDG.SequenceYear = ARE.SequenceYear 
                              AND ARE.AdmPersonId = @PrincipalInvestigator 

     WHERE     -- Anything ProjectEnded over a year ago, ignore
               DATEDIFF(dd, AI.ProjectEndDate ,GETDATE()) < 365 AND
               (@Institution IS NULL OR AI.InstitutionId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Institution))) AND
               (@Department IS NULL OR AI.DepartmentId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Department))) AND
               -- Need to get all Agreements where search PI has ANY role...
               (@PrincipalInvestigator = AI.PrincipalInvestigatorId OR 
               AI.SvcEntityId in ( SELECT DISTINCT SvcEntityId FROM dbo.vwAgreementRolesandEfforts 
                                   WHERE AdmPersonId = @PrincipalInvestigator)) 
                
     ORDER BY SortOrder               
               
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     
     DROP TABLE #tt_Agreements 
     
     IF @Error!=0
          RETURN -1
          
     -- Return Success
     RETURN 0
     
END
go

IF OBJECT_ID('dbo.RptInvestigatorActivity') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptInvestigatorActivity >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptInvestigatorActivity >>>'
go

GRANT EXECUTE ON dbo.RptInvestigatorActivity TO InsightReport,Insight
go

